-- This view maps the merged calendar and calendar_dates table into a single
-- view that maps the given service_date into the valid service_id(s)
--
-- This works for the current day to 60 days into the future as generated by
-- the recurisve date_range below
with recursive date_range as (
  select date('now', '-1 day') as inputdate
  union
  select date(inputdate, '+1 day') 
  from date_range
  where inputdate < date('now', '+60 days')
),
drc as (
  select
    cast(strftime('%w', inputdate) as int) as inputdatewd,
    cast(strftime('%Y%m%d', inputdate) as int) as inputdateyymmdd
  from date_range
),
calendar_base as (
  select source, service_id, inputdateyymmdd, 1 as exception_type
  from calendar as cal
  left join drc
  where
    drc.inputdateyymmdd between cal.start_date and cal.end_date
    and (
      (cal.sunday and drc.inputdatewd = 0)
      or (cal.monday and drc.inputdatewd = 1)
      or (cal.tuesday and drc.inputdatewd = 2)
      or (cal.wednesday and drc.inputdatewd = 3)
      or (cal.thursday and drc.inputdatewd = 4)
      or (cal.friday and drc.inputdatewd = 5)
      or (cal.saturday and drc.inputdatewd = 6)
    )
),
calendar_exceptions as (
  select source, service_id, inputdateyymmdd, exception_type from calendar_dates cd
    left join drc
    where cd.date = drc.inputdateyymmdd
) 
select source, service_date, service_id from (
  select
    -- Note groupby service_id in combination with some exception_type as 2
    -- flags an 'invalid'/canceled out service in calendar dates
    source, inputdateyymmdd as service_date, service_id,  max(exception_type) = 1 as valid_service
  from (
    select * from calendar_base
    union all
    select * from calendar_exceptions
  ) full_calendar
  group by source, inputdateyymmdd, service_id
) calendar_filtering where valid_service
